<html>
<head>
  <title>Evernote Export</title>
  <basefont face="微软雅黑" size="2" />
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  <meta name="exporter-version" content="YXBJ Windows/600753 (zh-CN, DDL); Windows/10.0.0 (Win64);"/>
  <style>
    body, td {
      font-family: 微软雅黑;
      font-size: 10pt;
    }
  </style>
</head>
<body>
<a name="404"/>

<div>
<span><div><div>       使用终端操作数据库</div><div><br/></div><div>    1.如何查看有什么数据库?    </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>show databases;</div></div><hr/><div>  </div><div>    2.如何选择数据库?   </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>use databasesName;</div></div><hr/><div> </div><div>    3.如何查看该数据库中有哪些表?   </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>show tables;</div></div><hr/><div>   </div><div><span>    </span>4.如何查询表中的数据?   </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>select * from tableName;</div></div><hr/><div>  </div><div><span>  </span>  5.如何退出数据库服务器?</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>exit;</div></div><div><br/></div><hr/><div>     6.如何在数据库服务器中创建自己的数据库?   </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>create database databaseName;</div></div><hr/><div> </div><div>      7.如何创建一个数据表? 创建一个pet表</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>create TABLE pet(</div><div>                   name VARCHAR(20),</div><div>                   owner VARCHAR(20),</div><div>                   specise VARCHAR(20),</div><div>                   sex CHAR(1),</div><div>                   brith DATAE,</div><div>                   death DATE );</div><div><br/></div><div><br/></div></div><div><br/></div><div><br/></div><div>      注意事项:</div><div>                     <span style="font-weight: bold;">1</span>:var()与varchar()的区别在于var()是定常的,哪怕存储的字符串没有达到&quot;()&quot;中数字的上限,var()依然会占用空格来填充空间.而varchar()则是不定长的,没有达到&quot;()&quot;中的上限则会自动去掉后面的空格;</div><div>                     <span style="font-weight: bold;">2</span>:性别不要用:sex 要用:gender  一个是性 一个是性别;</div><div>                   <span style="font-weight: bold;">  3:</span>定义最后一个字段的时候不要加&quot;,&quot;;</div><div>                     <span style="font-weight: bold;">4</span>:上面的&quot;VAR&quot;,&quot;VARCHAR&quot;,&quot;DATE&quot;可以用小写.不过最好用大写来表示区分关键字,若不然也许写到后面你自己都不知道这个词是数据库中的关键字还是你自己自定义的一些数据,同时一定要用英文的标点符号也必须半角输入</div><hr/><div>    8.如何查看数据表的架构?   </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>describe tableName;</div><div> 说明:</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| Field | Type        | Null | Key | Default | Extra |</div><div>+-------+-------------+------+-----+---------+-------+</div><div> Field    :    字段的名称</div><div>  Type     :    字段的类型,可以有int    var    varchar    </div><div>  Key      :    是否是关键字 如可以定义为:  primary key 或者 unique key   ...</div><div>Default: :    若是该字段没有主动设置值的时候,该字段的默认值是什么?</div></div><div> </div><hr/><div>    9.如何插入数据?</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div> INSERT INTO pet VALUES('kk','cc','dog','1','1998-8-2',null);</div></div><div>           +------+-------+---------+------+------------+-------+</div><div>           | name | owner | specise | sex     | brith           | death |</div><div>           +------+-------+---------+------+------------+-------+</div><div>           | kk       | cc       | dog      | 1        | 1998-08-02 | NULL|</div><div>           +------+-------+---------+------+------------+-------+</div><div>       注意:</div><div>           NULL:代表的是空,表示该字段还没有数据.千万不要主动填写'NULL',这代表你的字段有一个值叫做'null'.</div><div><br/></div><div>               其实还有一种写法:</div><div>                 </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><font color="#5898FF">INSERT INTO pet(name,owner) VALUES ('xx','cc');</font></div><div><font color="#5898FF">代表我只在name和owner字段上面插入的一条,其他皆为NULL/默认值的数据</font></div></div><div>              </div><hr/><div>    10.mysql 常用数据类型</div><div>           注意:金钱最好用int/bigint(整数,单位用分,拿出来进行*100换成元),千万不要直接用浮点,会有精度损失.</div><div><br/></div><hr/><div>    11.如何删除数据</div><div>       先插入数据:</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>       INSERT INTO pet VALUES('kk1','cc1','dog1','1','1998-1-2',null);</div><div>       INSERT INTO pet VALUES('kk2','cc2','dog2','2','1998-2-2',null);</div><div>       INSERT INTO pet VALUES('kk3','cc3','dog3','1','1998-3-2','1998-12-2');</div><div>       INSERT INTO pet VALUES('kk4','cc4','dog4','2','1998-4-2',null);</div></div><div><br/></div><div><br/></div><div>       删除语句:</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>DELETE FROM tablesName WHRER 条件;</div></div><div>     </div><div>        修改数据:</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>UPDATE tableName SET 字段1=值1,字段2=值2 ... WHERE 条件;</div></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><hr/><div>总结:1.table的操作 2.表操作的总结</div><hr/><div>  12.mysql建表中的约束</div><div>         <span style="font-weight: bold;">  1.主键约束:</span></div><div>                 它能够<span style="font-weight: bold;">唯一确定</span>一张表中的一条记录,增加主键约束之后,就可以使得字段不重复而且不为空   </div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">create table user(</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">    id int PRIMARY KEY,</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">    name VARCHAR(20)</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">);</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">INSERT INTO user VALUES (1,'张三');</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div><div>+----+------+</div><div>| id | name |</div><div>+----+------+</div><div>|  1 | 张三 |</div><div>+----+------+</div><div><br/></div><div>运行DESCRIBE user;</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| Field | Type        | Null | Key | Default | Extra |</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| id    | int(11)     | NO   | PRI | NULL    |       |</div><div>| name  | varchar(20) | YES  |     | NULL    |       |</div><div>+-------+-------------+------+-----+---------+-------+</div><div>发现 id是不可以为null 而且 key的值 也变为:PRI(primary)</div></div><div>          </div><div><span style="font-weight: bold;"><span>    </span><span>    </span><span>    </span>2.复合主键:</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE user2(</div><div>    id INT,</div><div>    name VARCHAR(20),</div><div>    password VARCHAR(20),</div><div>    PRIMARY key(id,name)</div><div>);</div><div><br/></div><div>运行DESCRIBE user2;</div><div>+----------+-------------+------+-----+---------+-------+</div><div>| Field    | Type        | Null | Key | Default | Extra |</div><div>+----------+-------------+------+-----+---------+-------+</div><div>| id       | int(11)     | NO   | PRI | NULL    |       |</div><div>| name     | varchar(20) | NO   | PRI | NULL    |       |</div><div>| password | varchar(20) | YES  |     | NULL    |       |</div><div>+----------+-------------+------+-----+---------+-------+</div><div><br/></div><div>INSERT INTO user2 VALUES (1,'老王','123456');</div><div>INSERT INTO user2 VALUES (2,'老王','123456');</div><div><br/></div><div>+----+------+----------+</div><div>| id | name | password |</div><div>+----+------+----------+</div><div>|  1 | 老王 | 123456   |</div><div>|  2 | 老王 | 123456   |</div><div>+----+------+----------+</div></div><div>说明了复合主键只要所有的字段都不是相同的情况下可以允许其中的字段重复:</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>INSERT INTO user2 VALUES (1,'老李','123456');</div><div><br/></div><div>SELECT * FROM user2;</div><div>+----+------+----------+</div><div>| id | name | password |</div><div>+----+------+----------+</div><div>|  1 | 老李 | 123456   |</div><div>|  1 | 老王 | 123456   |</div><div>|  2 | 老王 | 123456   |</div><div>+----+------+----------+</div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">场景:表中有班级号以及学生座位号,我们可以用班级号+学生的座位号可以准确的定位一个学生,如:(1班5号可以准确的确定一个学生)</font></span></div></div><div>          </div><div>            <span style="font-weight: bold;">3.自增约束:</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE user3(</div><div>    id INT PRIMARY KEY AUTO_INCREMENT,</div><div>    name VARCHAR(20)</div><div>);</div><div><br/></div><div>运行DESCRIBE user3;</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| Field | Type        | Null | Key | Default | Extra          |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| id    | int(11)     | NO   | PRI | NULL    | <font color="#5898FF">auto_increment</font> |</div><div>| name  | varchar(20) | YES  |     | NULL    |                |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div><br/></div><div>INSERT INTO user3(name) VALUES('张三');</div><div>INSERT INTO user3(name) VALUES('李四');</div><div>+----+------+</div><div>| id | name |</div><div>+----+------+</div><div>|  1 | 张三 |</div><div>|  2 | 李四 |</div><div>+----+------+</div><div>没有自定义id值 但是自动生成了id</div></div><div>        <span style="font-weight: bold;">  </span></div><div><span style="font-weight: bold;"><span>    </span><span>    </span><span>    </span>4.唯一约束:</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE user5(</div><div>    id INT PRIMARY KEY AUTO_INCREMENT,</div><div>    name VARCHAR(20)</div><div>);</div><div>运行 DESCRIBE user5;</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| Field | Type        | Null | Key | Default | Extra          |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| id    | int(11)     | NO   | PRI | NULL    | auto_increment |</div><div>| name  | varchar(20) | YES  |     | NULL    |                |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">新增name为唯一约束:</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">ALTER TABLE user5 ADD UNIQUE(name);</span></div><div>运行 DESCRIBE user5;</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| Field | Type        | Null | Key | Default | Extra          |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| id    | int(11)     | NO   | PRI | NULL    | auto_increment |</div><div>| name  | varchar(20) | YES  | <font color="#5898FF">UNI</font> | NULL    |                |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>测试:插入数据</div><div>INSERT INTO user5(name) VALUES ('cc');</div><div>运行 SELECT * FROM user5; 查看结果:</div><div>+----+------+</div><div>| id | name |</div><div>+----+------+</div><div>|  1 | cc   |</div><div>+----+------+</div><div>再次插入INSERT INTO user5(name) VALUES ('cc');</div><div><font color="#000000">出现:ERROR 1062 (23000): Duplicate entry 'cc' for key 'name'</font></div><div><br/></div><div>换个试试 INSERT INTO user5(name) VALUES ('aa');</div><div>运行 SELECT * FROM user5; 查看结果:</div><div>+----+------+</div><div>| id | name |</div><div>+----+------+</div><div>|  3 | aa   |</div><div>|  1 | cc   |</div><div>+----+------+</div><div><font color="#5898FF">总结一下:</font></div><div>    <font color="#5898FF">主键约束(primary key)中包含了唯一约束</font></div><div><font color="#5898FF">场景:业务需求:设计一张用户注册表,用户姓名必须要用手机号来注册,而且手机号和用户名称都不能为空,那么:</font></div><div><font style="color: rgb(88, 152, 255);">CREATE TABLE user_test(</font></div><div><font style="color: rgb(88, 152, 255);">    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',</font></div><div><font style="color: rgb(88, 152, 255);">    name VARCHAR(20)</font> <font color="#5898FF">NOT NULL</font> <font color="#5898FF">COMMENT'用户姓名,不能为空',</font></div><div><font style="color: rgb(88, 152, 255);">    phone_number VARCHAR(20) UNIQUE NOT NULL COMMENT'用户手机,不能重复且不能为空'</font></div><div><font color="#5898FF">);</font></div><div><font style="color: rgb(88, 152, 255);">运行 DESCRIBE user_test;</font></div><div><font color="#5898FF">+--------------+-------------+------+-----+---------+----------------+</font></div><div><font color="#5898FF">| Field        | Type        | Null | Key | Default | Extra          |</font></div><div><font color="#5898FF">+--------------+-------------+------+-----+---------+----------------+</font></div><div><font color="#5898FF">| id           | int(11)     | NO   | PRI | NULL    | auto_increment |</font></div><div><font color="#5898FF">| name         | varchar(20) | NO   |     | NULL    |                |</font></div><div><font color="#5898FF">| phone_number | int(11)     | NO   | UNI | NULL    |                |</font></div><div><font color="#5898FF">+--------------+-------------+------+-----+---------+----------------+</font></div><div><font color="#5898FF">这样的话就达到了每一个手机号都只能出现一次,达到了每个手机号只能被注册一次.</font></div><div><span style="font-size: 9pt; color: rgb(88, 152, 255); font-family: Monaco;">用户姓名可以重复,但是手机号码却不能重复,复合正常的逻辑需求</span></div></div><div>           </div><div><span style="font-weight: bold;">           5.非空约束:</span></div><div>           在上面的蓝字中已经添加了非空约束: NOT NULL;</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>name和phone_number都设置了非空,先只设置name参数不设置phone_number参数试一试</div><div>INSERT INTO user_test (name) VALUES ('张三');</div><div>会出现Field 'phone_number' doesn't have a default value</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">两个非空参数一起设置:</span></div><div>INSERT INTO user_test (name,phone_number) VALUES ('张三','12345678901');</div><div>+----+------+--------------+</div><div>| id | name | phone_number |</div><div>+----+------+--------------+</div><div>|  1 | 张三 | 12345678901  |</div><div>+----+------+--------------+</div></div><div>           </div><div>           <b>6.默认约束</b></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE user6(</div><div>   id int PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',</div><div>    name VARCHAR(20) NOT NULL COMMENT'用户姓名不能为空',</div><div>    phone_number VARCHAR(20) NOT NULL COMMENT'用户手机号,不能为空',</div><div>    status INT DEFAULT 0 COMMENT'用户状态0:启用 1:禁封 默认:0'</div><div>);</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">运行DESCRIBE user6;</span></div><div>+--------------+-------------+------+-----+---------+----------------+</div><div>| Field        | Type        | Null | Key | Default | Extra          |</div><div>+--------------+-------------+------+-----+---------+----------------+</div><div>| id           | int(11)     | NO   | PRI | NULL    | auto_increment |</div><div>| name         | varchar(20) | NO   |     | NULL    |                |</div><div>| phone_number | varchar(20) | NO   |     | NULL    |                |</div><div>| status       | int(11)     | YES  |     | 0       |                |</div><div>+--------------+-------------+------+-----+---------+----------------+</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">插入数据:</span></div><div><span style="font-family: Monaco; font-size: 9pt;">INSERT INTO user6(name,phone_number) VALUES ('aa','123');</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">INSERT INTO user6(name,phone_number) VALUES('bb','1234');</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">INSERT INTO user6(name,phone_number) VALUES('cc','1263456');</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">查看数据:SELECT * FROM user6;</span></div><div>+----+------+--------------+--------+</div><div>| id | name | phone_number | status |</div><div>+----+------+--------------+--------+</div><div>|  1 | aa   | 123          |      0 |</div><div>|  2 | bb   | 1234         |      0 |</div><div>|  3 | cc   | 1263456      |      0 |</div><div>+----+------+--------------+--------+</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">我们没有设置status的值,但是给我们创建了默认值 0.</span></div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">应用场景:</font></span></div><div><font color="#5898FF">业务需求:找正常的用户,对这些正常用户进行发放优惠卷或者积分之类的东西,而被禁封的用户我们不让其参加多动.</font></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">我们想要封用户只要将status的值从0改为1就行了,当然我们取用户的时候必须要先判断status是否是0.若是1.说明该用户已经被禁封.</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">先封手机号为'1234'的用户:</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">UPDATE user6 SET status = 1 WHERE phone_number= '1234';</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">SELECT * FROM user6;</font></span></div><div><font color="#5898FF">+----+------+--------------+--------+</font></div><div><font color="#5898FF">| id | name | phone_number | status |</font></div><div><font color="#5898FF">+----+------+--------------+--------+</font></div><div><font color="#5898FF">|  1 | aa   | 123          |      0 |</font></div><div><font color="#5898FF">|  2 | bb   | 1234         |      1 |</font></div><div><font color="#5898FF">|  3 | cc   | 1263456      |      0 |</font></div><div><font color="#5898FF">+----+------+--------------+--------+</font></div><div><font color="#5898FF">status为1,说明用户已经被封,该用户不可以参加活动</font></div><div><font color="#5898FF"><br/></font></div><div><font><span style="font-size: 10pt; color: rgb(88, 152, 255); font-family: Monaco;">我们取用户的时候加上status的判断</span><font color="#5898FF"><span style="font-size: 10pt; font-family: Monaco;">,</span><font style="font-size: 11pt;">如:</font></font></font></div><div><span style="font-size: 9pt; color: rgb(88, 152, 255); font-family: Monaco;">SELECT * FROM user6 WHERE status = 0;</span></div><div><font color="#5898FF">+----+------+--------------+--------+</font></div><div><font color="#5898FF">| id | name | phone_number | status |</font></div><div><font color="#5898FF">+----+------+--------------+--------+</font></div><div><font color="#5898FF">|  1 | aa   | 123          |      0 |</font></div><div><font color="#5898FF">|  3 | cc   | 1263456      |      0 |</font></div><div><font color="#5898FF">+----+------+--------------+--------+</font></div><div><span style="font-size: 9pt; color: rgb(88, 152, 255); font-family: Monaco;"><br/></span></div></div><div><br/></div><div>         <span style="font-weight: bold;">  7.外键约束</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE classes(</div><div>    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'班级表id',</div><div>    name VARCHAR(20) COMMENT'班级名称'</div><div>);</div><div>运行DESCRIBE classes;</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| Field | Type        | Null | Key | Default | Extra          |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| id    | int(11)     | NO   | PRI | NULL    | auto_increment |</div><div>| name  | varchar(20) | YES  |     | NULL    |                |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div><br/></div><div>CREATE TABLE student(</div><div>   id INT PRIMARY KEY AUTO_INCREMENT COMMENT'学生表id',</div><div>   name VARCHAR(20) COMMENT'学生姓名',</div><div>    class_id int COMMENT'教室id,这张表中的class_id是classes表中id的值',</div><div>    FOREIGN KEY (class_id) REFERENCES classes(id)</div><div>);</div><div>//FOREIGN :外来  REFERENCES:应用,参考</div><div>运行DESCRIBE student;</div><div>+----------+-------------+------+-----+---------+----------------+</div><div>| Field    | Type        | Null | Key | Default | Extra          |</div><div>+----------+-------------+------+-----+---------+----------------+</div><div>| id       | int(11)     | NO   | PRI | NULL    | auto_increment |</div><div>| name     | varchar(20) | YES  |     | NULL    |                |</div><div>| class_id | int(11)     | YES  | <font color="#5898FF">MUL</font> | NULL    |                |</div><div>+----------+-------------+------+-----+---------+----------------+</div><div><br/></div><div><b>班级</b>插入数据:</div><div>INSERT INTO CLASSES (name) VALUES ('一班');</div><div>INSERT INTO CLASSES (name) VALUES ('二班');</div><div>INSERT INTO CLASSES (name) VALUES ('三班');</div><div>INSERT INTO CLASSES (name) VALUES ('四班');</div><div>查看数据 SELECT * FROM classes;</div><div>+----+------+</div><div>| id | name |</div><div>+----+------+</div><div>|  1 | 一班 |</div><div>|  2 | 二班 |</div><div>|  3 | 三班 |</div><div>|  4 | 四班 |</div><div>+----+------+</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><b>学生</b>插入数据:</span></div><div>INSERT INTO student (name,class_id) VALUES ('小赵',1);</div><div>INSERT INTO student (name,class_id) VALUES ('小钱',2);</div><div>INSERT INTO student (name,class_id) VALUES ('小孙',3);</div><div>INSERT INTO student (name,class_id) VALUES ('小李',4);</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">查看数据 SELECT * FROM student;</span></div><div>+----+------+----------+</div><div>| id | name | class_id |</div><div>+----+------+----------+</div><div>|  1 | 小赵 |        1 |</div><div>|  2 | 小钱 |        2 |</div><div>|  3 | 小孙 |        3 |</div><div>|  4 | 小李 |        4 |</div><div>+----+------+----------+</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">若是像插入班级为5的数据 如:</span></div><div>INSERT INTO student (name,class_id) VALUES ('小周',<font color="#5898FF">5</font>);</div><div>报错: Cannot add or update a child row</div><div><br/></div><div>我们删除正在被学生表引用的'四班'试试:</div><div>DELETE classes WHERE name = '四班';</div><div>出现:Cannot delete or update a parent row:不能删除主表中的行</div><div><font color="#5898FF"><br/></font></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">我们先删除学生表中的 '小李'从而解除班级中'四班'的外键约束,再来删除'四班'(因为小李引用了四班)</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font style="color: rgb(88, 152, 255);">DELETE FROM student WHERE name = '小李';</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">再次删除classes表中的'四班';</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">DELETE FROM classes WHERE name = '四班';</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font style="color: rgb(88, 152, 255);">最后: SELECT * FROM classes;</font></span></div><div><font color="#5898FF">+----+------+</font></div><div><font color="#5898FF">| id | name |</font></div><div><font color="#5898FF">+----+------+</font></div><div><font color="#5898FF">|  1 | 一班 |</font></div><div><font color="#5898FF">|  2 | 二班 |</font></div><div><font color="#5898FF">|  3 | 三班 |</font></div><div><font color="#5898FF">+----+------+</font></div><div><font color="#5898FF">'四班'被成功删除!</font></div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#000000">总结:</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#000000">1.主表中没有的数据,在附表中,是不可以使用的.</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#000000">2.主表中记录的数据现在正在被附表所引用,那么主表中正在被引用的数据不可以被删除</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#000000">3.若要想删除,先将附表中的数据删除在删除主表数据</font></span></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">4.对于外键约束大家可以联想 省,市 来进行联想 (市必须要依赖于省,只要省还有一个市在引用,那么就不可以删除省,要不然市就没有省了. 那么我们想删除省,必须要将该省下所有的市全部删除之后,才可以删除这个省)</font></span></div></div><div><br/></div><div><span style="font-weight: bold;">            8.如何建表之后添加主键约束</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE user4(</div><div>    id INT,</div><div>    name VARCHAR(20)</div><div>);</div><div>运行DESCRIBE user4;</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| Field | Type        | Null | Key | Default | Extra |</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| id    | int(11)     | YES  |     | NULL    |       |</div><div>| name  | varchar(20) | YES  |     | NULL    |       |</div><div>+-------+-------------+------+-----+---------+-------+</div><div><br/></div><div>加入主键约束:</div><div>ALTER TABLE user4 add PRIMARY KEY(id);</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">再次运行DESCRIBE user4;</span></div><div>+-------+-------------+------+-----+---------+-------+</div><div>| Field | Type        | Null | Key | Default | Extra |</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| id    | int(11)     | NO   | <font color="#5898FF">PRI</font> | NULL    |       |</div><div>| name  | varchar(20) | YES  |     | NULL    |       |</div><div>+-------+-------------+------+-----+---------+-------+</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">删除主键约束:</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">ALERT TABLE user4 DROP PRIMARY KEY;</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">运行DESCRIBE user4查看表结构:</span></div><div>+-------+-------------+------+-----+---------+-------+</div><div>| Field | Type        | Null | Key | Default | Extra |</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| id    | int(11)     | NO   |     | NULL    |       |</div><div>| name  | varchar(20) | YES  |     | NULL    |       |</div><div>+-------+-------------+------+-----+---------+-------+</div><div><br/></div><div>使用modify 修改字段.添加约束:</div><div>ALTER TABLE user4 MODIFY id INT PRIMARY key;</div><div>使用DESCRIBE user4 查看表结构:</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| Field | Type        | Null | Key | Default | Extra |</div><div>+-------+-------------+------+-----+---------+-------+</div><div>| id    | int(11)     | NO   | <font color="#5898FF">PRI</font> | NULL    |       |</div><div>| name  | varchar(20) | YES  |     | NULL    |       |</div><div>+-------+-------------+------+-----+---------+-------+</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">给主键设置自增长:</span></div><div>ALTER TABLE user4 MODIFY id INT AUTO_INCREMENT;</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">运行 DESCRIBE user4 查看表结构:</span></div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| Field | Type        | Null | Key | Default | Extra          |</div><div>+-------+-------------+------+-----+---------+----------------+</div><div>| id    | int(11)     | NO   | <font color="#5898FF">PRI</font> | NULL    | <font color="#5898FF">auto_increment</font> |</div><div>| name  | varchar(20) | YES  |     | NULL    |                |</div><div>+-------+-------------+------+-----+---------+----------------+</div></div><div><br/></div></div><div><br/></div></span>
</div></body></html> 